package com.taxinfo.service.impl;

import cn.hutool.json.JSONObject;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.taxinfo.dao.SoftwareMapModelMapper;
import com.taxinfo.dao.StandardSubMapper;
import com.taxinfo.dao.SubMatchingMapper;
import com.taxinfo.pojo.dto.SubMatchingDto;
import com.taxinfo.pojo.dto.SystemSubMatchingBO;
import com.taxinfo.pojo.dto.SystemSubMatchingList;
import com.taxinfo.pojo.vo.SoftwareMapModel;
import com.taxinfo.pojo.vo.StandardSub;
import com.taxinfo.pojo.vo.SubMatching;
import com.taxinfo.service.SubMatchingService;
import com.taxinfo.utils.HttpClientUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author wanghy
 * @since 2025-01-20
 */
@Service("SubMatchingServiceImpl")
public class SubMatchingServiceImpl extends ServiceImpl<SubMatchingMapper, SubMatching> implements SubMatchingService {

    @Autowired
    private SubMatchingMapper subMatchingMapper;
    @Autowired
    private StandardSubMapper standardSubMapper;

    @Autowired
    private SoftwareMapModelMapper softwareMapModelMapper;

    @Value("${modelFile.subMapMode}")
    private String subMapMode;

    @Value("${url.submitSubMap}")
    private String uploadUrl;

    @Override
    public List<SubMatchingDto> selectAllData(String category) {
        return subMatchingMapper.selectAllData(category);
    }

    @Override
    public List<SubMatchingDto> selectAllData(String compSubName, String subName, String ifMatch,String category) {
        return subMatchingMapper.selectDataBySelect(compSubName, subName, ifMatch,category);
    }


    @Override
    public String initXlsx(File file) {
        // 定位信息获取
        SoftwareMapModel softwareMapModel = softwareMapModelMapper.selectOne(new QueryWrapper<>());

        XSSFWorkbook wb = null;
        String rtrVal = "";
        InputStream fis = null;
        Workbook workbook = null;
        try {
            LambdaQueryWrapper<StandardSub> wrapper = new LambdaQueryWrapper<StandardSub>();
            wrapper.eq(StandardSub::getDecType, "余额表科目");
            List<StandardSub> standardSubs = standardSubMapper.selectList(wrapper);
            subMatchingMapper.deleteAll("余额表科目");
            Map<String, StandardSub> standardSubMap = standardSubs.stream().collect(Collectors.toMap(StandardSub::getSubName, standardSub -> standardSub));
            fis = new FileInputStream(file);
            workbook = WorkbookFactory.create(fis);
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表

            //模版匹配处理
            List<SubMatching> subMatchings =  this.getRowColumInfo(softwareMapModel,sheet);
            if(null == subMatchings) {
                rtrVal = "模版匹配出错，请检查模版格式是否正确！";
                return rtrVal;
            }
            // 插入数据
            this.fitSubString(subMatchings,standardSubMap,true);

        } catch (Exception e) {
            rtrVal = "导入数据出错，请检查数据格式是否正确！";
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
                workbook = null;
                fis.close();
                fis = null;
            } catch (IOException e) {
                rtrVal = "导入数据出错，文件关闭异常！";
                e.printStackTrace();
            }
        }
        return rtrVal;
    }

    @Override
    public String exportSubMatchFile(String targetFilePath) {
        String msg = "";
        //获取模版路径
        String filePath = subMapMode.concat(File.separator).concat("科目匹配映射模版.xlsx");
        String jarPath = System.getProperty("java.class.path");
        String currentPath = "";
        if(jarPath.endsWith(".jar" ) && !jarPath.contains(";")){
            String jarDirectory = new File(jarPath).getAbsolutePath();
            currentPath = jarDirectory.substring(0,jarDirectory.lastIndexOf("\\"));
            filePath = currentPath.concat(filePath);
        }else{
            try {
                currentPath = new File(".").getCanonicalPath();
            } catch (IOException e) {
                e.printStackTrace();
            }
            filePath = currentPath.concat(filePath);
        }

        Path sourcePath = Paths.get(filePath);
        XSSFWorkbook wb = null;
        FileInputStream is = null;
        FileOutputStream fileOut = null;
        try {
            if (Files.exists(sourcePath)) {
                is = new FileInputStream(new File(filePath));
                wb = new XSSFWorkbook(is);
                Sheet sheet = wb.getSheetAt(0);

                List<SubMatching> subMatchingDtos =  subMatchingMapper.selectList(new QueryWrapper());

                // 循环写入数据
                for (int i = 0; i < subMatchingDtos.size(); i++) {
                    SubMatching subMatchingDto = subMatchingDtos.get(i);
                    Row row = sheet.createRow(i+1);
                    Cell cell1 = row.createCell(0);
                    cell1.setCellValue(subMatchingDto.getCompSubNo());
                    Cell cell2 = row.createCell(1);
                    cell2.setCellValue(subMatchingDto.getCompSubName());
                    Cell cell3 = row.createCell(2);
                    cell3.setCellValue(subMatchingDto.getSubNo());
                    Cell cell4 = row.createCell(3);
                    cell4.setCellValue(subMatchingDto.getSubName());
                    Cell cell5 = row.createCell(4);
                    cell5.setCellValue(subMatchingDto.getSubType());
                    Cell cell6 = row.createCell(5);
                    cell6.setCellValue(subMatchingDto.getSwitchFlg());
                }

                // 保存修改后的工作簿到新文件
                fileOut = new FileOutputStream(targetFilePath);
                wb.write(fileOut);
                System.out.println("文件已成功保存到: " + targetFilePath);
            }
        } catch (IOException e) {
            msg="导出文件失败";
            e.printStackTrace();
        }finally {
            try {
                if (is!= null) {
                    is.close();
                }
                if (wb!= null) {
                    wb.close();
                }
                if(null!=fileOut) {
                    fileOut.close();
                }
            } catch (IOException e) {
                msg = "关闭文件异常！";
                e.printStackTrace();
            }
        }
        return msg;
    }

    /**
     * 初始化列
     * @param colNum
     * @param changed
     * @param row
     * @param rowColNum
     * @param entSubName
     * @param entFirtNum
     * @param entEndNum
     */
    private boolean initColumns(Row row,int[] rowColNum,int[] entSubName,int[] entFirtNum,int[] entEndNum,int realColumn, Map<String, List<String>> columnInfo) {

        int iNum = 0;
        int jNum = 0;
        int kNum = 0;
        int lNum = 0;
        boolean changed = false;
        for (int k = 0; k < row.getLastCellNum() ; k++) {
            if(null == row.getCell(k)){
                break;
            }
            String cellValue = getCellInfo(row.getCell(k));
            realColumn = k;
            if (StringUtils.isBlank(cellValue)) {
                break;
            }else{
                cellValue = cellValue.replaceAll("\\s", "").replace("　", "").replaceAll("“-”","").replaceAll("“－”","");;
            }

            if (null != row.getCell(k) && columnInfo.get("sorted").contains(cellValue)) {
                changed = true;
                if (iNum == 0) {
                    rowColNum[0] = k;
                } else {
                    rowColNum[1] = k;
                }
                iNum = 1;
            }

            if (null != row.getCell(k) && columnInfo.get("names").contains(cellValue)) {
                changed = true;
                if (jNum == 0) {
                    entSubName[0] = k;
                } else {
                    entSubName[1] = k;
                }
                jNum = 1;
            }

            if (null != row.getCell(k) && columnInfo.get("firtYears").contains(cellValue)) {
                changed = true;
                if (kNum == 0) {
                    entFirtNum[0] = k;
                } else {
                    entFirtNum[1] = k;
                }
                kNum = 1;
            }

            if (null != row.getCell(k) && columnInfo.get("endYears").contains(cellValue)) {
                changed = true;
                if (lNum == 0) {
                    entEndNum[0] = k;
                } else {
                    entEndNum[1] = k;
                }
                lNum = 1;
            }
        }
        return changed;
    }

    /**
     * 生成数据
     * @param row
     * @param rowColNum
     * @param entSubName
     * @param entFirtNum
     * @param entEndNum
     * @param types
     * @return
     */
    private SubMatching initSubMatching(Row row, int rowNo, int entSubName,String types, String prefix) {
        SubMatching matching =null;
        if (entSubName != -1) {
            String entName = "";
            if(null != row.getCell(entSubName)){
                entName = getCellInfo(row.getCell(entSubName)).replaceAll("\\s", "").replaceAll("　","").replaceAll("“-”","").replaceAll("“－”","");
            }

            if (StringUtils.isNotBlank(entName)) {
                matching = new SubMatching();
                matching.setCompSubName(entName);

                if (rowNo != -1) {
                    String cellVal = "";
                    if(null !=row.getCell(rowNo)){
                        cellVal = getCellInfo(row.getCell(rowNo));
                    }

                    if(StringUtils.isBlank(cellVal)) {
                        matching.setCompSubNo(prefix.concat("-").concat(String.format("%02d", row.getRowNum())));
                    }else{
                        matching.setCompSubNo(prefix.concat("#").concat(cellVal));
                    }
                } else {
                    matching.setCompSubNo(prefix.concat(String.format("%03d", row.getRowNum())));
                }
                matching.setDecType(types);
            }
        }
        return matching;
    }

    @Override
    public String initAccXlsx(File file, Map<String, List<String>> columnInfo) {

        XSSFWorkbook wb = null;
        String rtrVal = "";
        InputStream fis = null;
        Workbook workbook = null;

        try {
            fis = new FileInputStream(file);
            workbook = WorkbookFactory.create(fis);
            int sheetCount = workbook.getNumberOfSheets();
            subMatchingMapper.deleteAll("负债表科目");
            subMatchingMapper.deleteAll("利润表科目");

            for(int i = 0; i < sheetCount; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                int rowCount = sheet.getLastRowNum();
                int numMerger =   sheet.getNumMergedRegions();
                int realColumn = 0 ;

                //行次
                int[] rowColNum = {-1,-1};
                //企业科目名称
                int[] entSubName = {-1,-1};
                //期初额
                int[] entFirtNum = {-1,-1};
                //期末额
                int[] entEndNum = {-1,-1};
                if(sheet.getSheetName().contains("资产")) {
                    //检查行次是否存在
                    boolean changed = false;
                    List<SubMatching> subMatchings = new ArrayList<SubMatching>();
                    List<SubMatching> subMatchings1 = new ArrayList<SubMatching>();
                    for (int j = 0; j <= rowCount; j++) {
                        Row row = sheet.getRow(j);
                        if (!changed) {
                            //初始化列
                            changed =  this.initColumns(row,rowColNum,entSubName,entFirtNum,entEndNum,realColumn,columnInfo);

                        }else{
                            if(null == row){
                                break;
                            }
                            if(null == row.getCell(entSubName[0])){
                                break;
                            }
                            if(getCellInfo(row.getCell(entSubName[0])).contains("注:")|| getCellInfo(row.getCell(entSubName[0])).contains("注：") ) {
                                break;
                            }
                            SubMatching subMatching = this.initSubMatching(row, rowColNum[0], entSubName[0],"负债表科目","80");
                            if(null != subMatching){
                                subMatchings.add(subMatching);
                            }

                            //第二组数据
                            if(entSubName[1] != -1 ){
                                SubMatching subMatching1 = this.initSubMatching(row, rowColNum[1], entSubName[1],"负债表科目","81");
                                if(null != subMatching1){
                                    subMatchings1.add(subMatching1);
                                }
                            }

                        }
                    }
                    LambdaQueryWrapper<StandardSub> wrapper = new LambdaQueryWrapper<StandardSub>();
                    wrapper.eq(StandardSub::getDecType, "负债表科目");
                    List<StandardSub> standardSubs = standardSubMapper.selectList(wrapper);
                    Map<String, StandardSub> standardSubMap = standardSubs.stream().collect(Collectors.toMap(StandardSub::getSubName, standardSub -> standardSub));
                    subMatchings.addAll(subMatchings1);
                    this.fitSubString(subMatchings,standardSubMap,false);

                }else if(sheet.getSheetName().contains("利润")){
                    //检查行次是否存在
                    boolean changed = false;
                    List<SubMatching> subMatchings = new ArrayList<SubMatching>();
                    List<SubMatching> subMatchings1 = new ArrayList<SubMatching>();
                    for (int j = 0; j <= rowCount; j++) {
                        Row row = sheet.getRow(j);
                        if (!changed) {
                            //初始化列
                            changed =  this.initColumns(row,rowColNum,entSubName,entFirtNum,entEndNum,realColumn,columnInfo);

                        }else{
                            if(null == row){
                                break;
                            }
                            if(null == row.getCell(entSubName[0])){
                                break;
                            }
                            if(getCellInfo(row.getCell(entSubName[0])).contains("注:")|| getCellInfo(row.getCell(entSubName[0])).contains("注：") ) {
                                break;
                            }
                            SubMatching subMatching = this.initSubMatching(row, rowColNum[0], entSubName[0],"利润表科目","90");
                            if(null != subMatching){
                                subMatchings.add(subMatching);
                            }
                            //第二组数据
                            if(entSubName[1] != -1 ){
                                SubMatching subMatching1 = this.initSubMatching(row, rowColNum[1], entSubName[1],"利润表科目","91");
                                if(null != subMatching1){
                                    subMatchings1.add(subMatching1);
                                }
                            }
                        }
                    }
                    LambdaQueryWrapper<StandardSub> wrapper = new LambdaQueryWrapper<StandardSub>();
                    wrapper.eq(StandardSub::getDecType, "利润表科目");
                    List<StandardSub> standardSubs = standardSubMapper.selectList(wrapper);
                    Map<String, StandardSub> standardSubMap = standardSubs.stream().collect(Collectors.toMap(StandardSub::getSubName, standardSub -> standardSub));
                    subMatchings.addAll(subMatchings1);
                    this.fitSubString(subMatchings,standardSubMap,false);
                }else{
                    rtrVal = sheet.getSheetName().concat("#").concat(rtrVal);
                }
            }
            if(StringUtils.isNotBlank(rtrVal)){
                rtrVal = "0【".concat(rtrVal).concat("】,请检查Excel表格sheet名称，只有带有【资产负债表】和【利润表】的sheet数据才能导入");
            }
        } catch (Exception e) {
            e.printStackTrace();
            rtrVal = "导入数据失败";
        }finally {
            try {
                workbook.close();
                workbook = null;
                fis.close();
                fis = null;

            } catch (IOException e) {
                e.printStackTrace();
                rtrVal = "关闭Excel文件失败";
            }
        }
        return rtrVal;
    }

    @Override
    public String postList(SystemSubMatchingList subMatchingDtos) {
        String rtnVal = "";
        try {
            rtnVal =  HttpClientUtil.postList(this.uploadUrl,subMatchingDtos);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rtnVal;
    }

    @Override
    public String getList(String loginName) {
//        String rtnVal = "";
//        try {
//            HashMap<String, Object> params = new HashMap<String, Object>();
//            params.put("email",loginName);
//            rtnVal =  HttpClientUtil.get(this.getSubMapUrl,params);
//            if (StringUtils.isNotBlank(rtnVal)){
//                JSONObject jsonObj = new JSONObject(rtnVal);
//            }
//            //this.saveBatch()
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        return rtnVal;
        return "";
    }

    @Override
    public void insertMatch(SubMatching subMatching) {
        QueryWrapper<SubMatching> wrapper = new QueryWrapper<>();
        wrapper.eq("comp_sub_no",subMatching.getCompSubNo());
        subMatchingMapper.delete(wrapper);
        this.subMatchingMapper.insert(subMatching);
    }

    @Override
    public List<SubMatchingDto> selectAllDataByStatus(String status) {
        return subMatchingMapper.selectAllDataByStatus(status);
    }


    private String transformation(Object obj) {
        if (null == obj) {
            return "";
        } else {
            return String.valueOf(obj);
        }
    }

    private String[] getSpecialCharsBySplit(String input) {
        // 定义一个正则表达式，匹配非字母数字字符
        String regex = "[^\\p{Alpha}\\p{Digit}\\u4e00-\\u9fa5]";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(input);
        StringBuilder specialChars = new StringBuilder();
        String split = "";
        while (matcher.find()) {
            System.out.println("Special characters: " + matcher.group());
            split = matcher.group();
            System.out.println(" #@@@@@@@ = ".concat(split));
            if ("\\".equals(matcher.group())) {
                split = "\\\\";
            }
            if("（".equals(matcher.group())){
                split = "";
            }
            if("）".equals(matcher.group())){
                split = "";
            }
            if("(".equals(matcher.group())){
                split = "";
            }
            if(")".equals(matcher.group())){
                split = "";
            }
            break;
        }
        if ("".equals(split)) {
            return new String[]{input};
        } else {
            if("*".equals(split)) {
                split = "\\\\";
                input = input.replace("*","");
            }
            if("（".equals(split)){
                return new String[]{input};
            }
            String[] rtnVal = {};
            try{
                rtnVal = input.split(split);
            }catch (Exception ex){
                ex.printStackTrace();
            }

            return rtnVal;
        }
    }

    /**
     * 获取行号与列号
     *
     * @return
     */
    private List<SubMatching> getRowColumInfo(SoftwareMapModel softwareMapModel, Sheet sheet) {
        String charSubNo = softwareMapModel.getDestSubNo();
        String charSubName = softwareMapModel.getDestSubName();
        String charOpenBal = softwareMapModel.getDestOpenBal();
        String charCurrentDebit = softwareMapModel.getDestCurrentDebit();
        String charCurrentCredit = softwareMapModel.getDestCurrentCredit();
        String charCloseBal = softwareMapModel.getDestEndBal();

        String[] charOpenBals = charOpenBal.split("-");
        String[] charCurrentDebits = charCurrentDebit.split("-");
        String[] charCurrentCredits = charCurrentCredit.split("-");
        String[] charCloseBals = charCloseBal.split("-");
        //确认是否首行（标题行）
        int titleRowNum = 0;
        int titleSubNoColNum = 0;
        int titleSubNameColNum = 0;
        int titleOpenBalColNum = 0;
        int titleCurrentDebitColNum = 0;
        int titleCurrentCreditColNum = 0;
        int titleCloseBalColNum = 0;
        boolean nextline = false;
        for (Row row : sheet) {
            if(nextline){
                nextline = false;
                continue;
            }
            int charSubNoInt = 0;
            int charSubNameInt = 0;
            int charOpenBalInt = 0;
            int charCurrentDebitInt = 0;
            int charCurrentCreditInt = 0;
            int charCloseBalInt = 0;
            for (Cell cell : row) {
                if (cell.getCellType() == CellType.STRING) {
                    String cellValue = cell.getStringCellValue().trim();
                    if (cellValue.equals(charSubNo)) {
                        charSubNoInt = row.getRowNum() + 1;
                        titleSubNoColNum = cell.getColumnIndex();
                    } else if (cellValue.equals(charSubName)) {
                        charSubNameInt = row.getRowNum() + 1;
                        titleSubNameColNum = cell.getColumnIndex();
                    } else if (charOpenBal.contains(cellValue)) {
                        charOpenBalInt = row.getRowNum() + 1;
                        titleOpenBalColNum = cell.getColumnIndex();

                        if(charOpenBals.length>1){
                            Row nextRow = sheet.getRow(row.getRowNum()+1) ;
                            for (Cell nextCell : nextRow){
                                String cellValueNext = nextCell.getStringCellValue().trim();
                                if (cellValueNext.equals(charOpenBals[1]) && (nextCell.getColumnIndex() ==titleOpenBalColNum || nextCell.getColumnIndex() == titleOpenBalColNum+1)) {
                                    charOpenBalInt = nextRow.getRowNum() + 1;
                                    titleOpenBalColNum = nextCell.getColumnIndex();
                                    charSubNameInt = nextRow.getRowNum() + 1;
                                    charSubNoInt = nextRow.getRowNum() + 1;
                                    nextline=true;
                                    break;
                                }
                            }
                        }

                    } else if (charCurrentDebit.contains(cellValue)) {
                        charCurrentDebitInt = row.getRowNum() + 1;
                        titleCurrentDebitColNum = cell.getColumnIndex();

                        if(charCurrentDebits.length>1){
                            Row nextRow = sheet.getRow(row.getRowNum()+1) ;
                            for (Cell nextCell : nextRow){
                                String cellValueNext = nextCell.getStringCellValue().trim();
                                if(charCurrentDebits[0].equals(charCurrentCredits[0])){
                                    titleCurrentCreditColNum = cell.getColumnIndex();
                                    if (cellValueNext.equals(charCurrentDebits[1]) && (nextCell.getColumnIndex() == titleCurrentDebitColNum || nextCell.getColumnIndex() == titleCurrentDebitColNum+1)) {
                                        charCurrentDebitInt = nextRow.getRowNum() + 1;
                                        titleCurrentDebitColNum = nextCell.getColumnIndex();
                                    }else if (cellValueNext.equals(charCurrentCredits[1]) && (nextCell.getColumnIndex() == titleCurrentCreditColNum || nextCell.getColumnIndex() == titleCurrentCreditColNum+1)) {
                                        charCurrentCreditInt = nextRow.getRowNum() + 1;
                                        titleCurrentCreditColNum = nextCell.getColumnIndex();
                                    }
                                    charSubNameInt = nextRow.getRowNum() + 1;
                                    charSubNoInt = nextRow.getRowNum() + 1;
                                    nextline=true;
                                }else{
                                    if (cellValueNext.equals(charCurrentDebits[1]) && (nextCell.getColumnIndex() == titleCurrentDebitColNum || nextCell.getColumnIndex() == titleCurrentDebitColNum+1)) {
                                        charCurrentDebitInt = nextRow.getRowNum() + 1;
                                        titleCurrentDebitColNum = nextCell.getColumnIndex();

                                        charSubNameInt = nextRow.getRowNum() + 1;
                                        charSubNoInt = nextRow.getRowNum() + 1;
                                        nextline=true;
                                        break;
                                    }
                                }

                            }
                        }

                    } else if (charCurrentCredit.contains(cellValue)) {
                        charCurrentCreditInt = row.getRowNum() + 1;
                        titleCurrentCreditColNum = cell.getColumnIndex();
                        if(charCurrentCredits.length>1){
                            Row nextRow = sheet.getRow(row.getRowNum()+1) ;
                            for (Cell nextCell : nextRow){
                                String cellValueNext = nextCell.getStringCellValue().trim();
                                if (cellValueNext.equals(charCurrentCredits[1]) && (nextCell.getColumnIndex() == titleCurrentCreditColNum || nextCell.getColumnIndex() == titleCurrentCreditColNum+1)) {
                                    charCurrentCreditInt = nextRow.getRowNum() + 1;
                                    titleCurrentCreditColNum = nextCell.getColumnIndex();
                                    charSubNameInt = nextRow.getRowNum() + 1;
                                    charSubNoInt = nextRow.getRowNum() + 1;
                                    nextline=true;
                                    break;
                                }
                            }
                        }
                    } else if (charCloseBal.contains(cellValue)) {
                        charCloseBalInt = row.getRowNum() + 1;
                        titleCloseBalColNum = cell.getColumnIndex();
                        if(charCloseBals.length>1){
                            Row nextRow = sheet.getRow(row.getRowNum()+1) ;
                            for (Cell nextCell : nextRow){
                                String cellValueNext = nextCell.getStringCellValue().trim();
                                if (cellValueNext.equals(charCloseBals[1]) && (nextCell.getColumnIndex() == titleCloseBalColNum || nextCell.getColumnIndex() == titleCloseBalColNum+1)) {
                                    charCloseBalInt = nextRow.getRowNum() + 1;
                                    titleCloseBalColNum = nextCell.getColumnIndex();
                                    charSubNameInt = nextRow.getRowNum() + 1;
                                    charSubNoInt = nextRow.getRowNum() + 1;
                                    nextline=true;
                                    break;
                                }
                            }
                        }
                    }
                }
                if(cell.getColumnIndex()+1 == row.getLastCellNum()){
                    break;
                }
            }

            if (charSubNoInt == charSubNameInt
                    //&& charSubNoInt == charOpenBalInt
                    && charSubNoInt == charCurrentDebitInt
                    && charSubNoInt == charCurrentCreditInt
                    //&& charSubNoInt == charCloseBalInt
                    && charSubNoInt != 0 ) {
                //标题行
                titleRowNum = charSubNoInt;
                break;
            }
        }

        if(titleRowNum == 0){
            return null; // 未找到匹配项
        }

        //获取最后行号
        int lastNum = sheet.getLastRowNum();
        for (int i = lastNum; i > 0; i--) {
            Row row = sheet.getRow(i);
            if (null != row.getCell(titleSubNoColNum)) {
                try{
                    Double.valueOf(String.valueOf(row.getCell(titleSubNoColNum)));
                }catch (Exception e){
                    continue;
                }
                lastNum = row.getRowNum();
                break;
            }
        }

        List<SubMatching> subMatchingList = new ArrayList<SubMatching>();
        for(int rowNumber = titleRowNum; rowNumber<=lastNum; rowNumber++) {

            Row row = sheet.getRow(rowNumber);
            SubMatching match = new SubMatching();
            match.setCompSubNo(getCellInfo(row.getCell(titleSubNoColNum)).trim());
            match.setCompSubName(getCellInfo(row.getCell(titleSubNameColNum)).trim());
            match.setDecType("余额表科目");
            if(StringUtils.isNotBlank(match.getCompSubNo()) || StringUtils.isNotBlank(match.getCompSubName())){
                subMatchingList.add(match);
            }
//            Cell cellOpenBal = row.getCell(titleOpenBalColNum);
//            Cell cellCurrentDebit = row.getCell(titleCurrentDebitColNum);
//            Cell cellCurrentCredit = row.getCell(titleCurrentCreditColNum);
//            Cell cellCloseBal = row.getCell(titleCloseBalColNum);
        }
        return subMatchingList;
    }

    /**
     * 获取单元格信息
     * @param cell
     * @return
     */
    private String getCellInfo(Cell cell) {
       String cellValue = "";
       if (cell.getCellType() == CellType.STRING) {
           cellValue = cell.getStringCellValue();
       }else if (cell.getCellType() == CellType.NUMERIC) {
           // 将科学计数法转换为常规数字格式
           double value = cell.getNumericCellValue();
           cellValue = new DecimalFormat("###.##").format(value); // 格式化为常规数字格式
       }else{
           ;
       }
        return cellValue;
    }

    /**
     * 处理数据
     * @param subMatchings
     * @param standardSubMap
     */
    private void fitSubString(List<SubMatching> subMatchings,Map<String, StandardSub> standardSubMap,boolean checkFlg){
        //数据映射
        for (SubMatching subMatching : subMatchings) {
            String compSubName = "";
            if(subMatching.getCompSubName().contains(subMatching.getCompSubNo())){
                compSubName = subMatching.getCompSubName().substring(subMatching.getCompSubNo().length()+1);
            }else{
                compSubName =subMatching.getCompSubName();
            }
            String[] comSubName = {compSubName};
            if(checkFlg){
                comSubName = getSpecialCharsBySplit(compSubName.replaceAll("\\s", "").replaceAll("　","").replaceAll("“-”","").replaceAll("“－”",""));
            }

            if (comSubName.length == 1) {
                StandardSub standardSub = standardSubMap.get(comSubName[0].trim());
                if (null != standardSub) {
                    subMatching.setSubNo(standardSub.getSubNo());
                    subMatching.setSubName(standardSub.getSubName());
                    subMatching.setSubType(standardSub.getSubType());
                } else {
                    subMatching.setSubNo("");
                    subMatching.setSubName("");
                    subMatching.setSubType("");
                }
            } else {
                subMatching.setSubNo("");
                subMatching.setSubName("");
                subMatching.setSubType("");
                LambdaQueryWrapper<StandardSub> wrapper1 = new LambdaQueryWrapper<StandardSub>();
                wrapper1.likeRight(StandardSub::getSubName,comSubName[0]);
                wrapper1.like(StandardSub::getSubName,comSubName[comSubName.length - 1]);
                List<StandardSub> standardSubs1 = standardSubMapper.selectList(wrapper1);
                if(null != standardSubs1 ) {
                    if(standardSubs1.size() == 1){
                        String[] subArr =  this.getSpecialCharsBySplit(standardSubs1.get(0).getSubName());
                        if(subArr.length > 1){
                            subMatching.setSubNo(standardSubs1.get(0).getSubNo());
                            subMatching.setSubName(standardSubs1.get(0).getSubName());
                            subMatching.setSubType(standardSubs1.get(0).getSubType());
                        }
                    }else {
                        if("医疗保险".equals(comSubName[comSubName.length - 1])){
                            for(StandardSub standard : standardSubs1){
                                String[] subArr =  this.getSpecialCharsBySplit(standard.getSubName());
                                if(subArr.length>1 && standard.getSubName().contains("基本医疗保险")){
                                    subMatching.setSubNo(standardSubs1.get(0).getSubNo());
                                    subMatching.setSubName(standardSubs1.get(0).getSubName());
                                    subMatching.setSubType(standardSubs1.get(0).getSubType());
                                }
                            };
                        }
                        if("养老保险".equals(comSubName[comSubName.length - 1])){
                            for(StandardSub standard : standardSubs1){
                                String[] subArr =  this.getSpecialCharsBySplit(standard.getSubName());
                                if(subArr.length>1 && standard.getSubName().contains ("基本养老保险")){
                                    subMatching.setSubNo(standardSubs1.get(0).getSubNo());
                                    subMatching.setSubName(standardSubs1.get(0).getSubName());
                                    subMatching.setSubType(standardSubs1.get(0).getSubType());
                                }
                            };
                        }
                    }
                }
            }
            if (StringUtils.isBlank(subMatching.getSubName()) && StringUtils.isBlank(subMatching.getSubNo())) {
                subMatching.setSwitchFlg("禁用");
            } else {
                subMatching.setSwitchFlg("启用");
            }
            subMatching.setStatus("0");
            subMatchingMapper.insert(subMatching);
        }
    }
}